USE [DSRSystem_Temp]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spPrepareTables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spPrepareTables]
GO

CREATE PROCEDURE [dbo].[spPrepareTables]
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SyncLog') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[SyncLog](
			[Id] [int] IDENTITY(1,1) NOT NULL,
			[LastSyncDate] [datetime] NULL,
			[LastUpdate] [datetime] NULL,
			[ElapsedTime] [int] NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.FinalizedCompanies') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[FinalizedCompanies](
		[CompanyId] uniqueidentifier NOT NULL,
	    [CompanyName] nvarchar(max) NOT NULL,
		[CapitalIQID] nvarchar(max) NOT NULL,
		[AnalystCommentary] nvarchar(max) NOT NULL,
		[LastCorporateDataUpdate] datetime NOT NULL,
		[LastFinalUpdate] datetime NOT NULL,
	    [LastCentralizedResearchUpdate] datetime NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplates') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplates](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[IndicatorName] [nvarchar](max) NOT NULL,
		[IndicatorNumber] [nvarchar](max) NOT NULL,
		[IndicatorSortKey] [int] NOT NULL,
		[ThemeId] [uniqueidentifier] NOT NULL,
		[ThemeName] [nvarchar](max) NOT NULL,
		[ThemeSortKey] [int] NOT NULL,
		[IndicatorCategory] [nvarchar](max) NOT NULL,
		[IndicatorCategorySortKey] int NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTemplateAnswerCategories') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTemplateAnswerCategories](
		[IndicatorId] [uniqueidentifier] NOT NULL,
		[IndicatorNumber] [nvarchar](max) NOT NULL,
		[Code] [char](1) NOT NULL,
		[Text] [nvarchar](max) NOT NULL,
		[Score] [int] NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Indicators') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[Indicators](
			[Id] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[TemplateId] uniqueidentifier NOT NULL,
			[Number] nvarchar(50) NOT NULL,
			[AnswerCategoryCode] nvarchar(max) NOT NULL,
			[AnswerCategoryText] nvarchar(max) NOT NULL,
			[AnswerCategoryScore] int NOT NULL,
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorComments') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorComments](
			[CommentId] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[IndicatorTemplateId] uniqueidentifier NOT NULL,
			[CommentNumber] int NOT NULL,
			[CommentText] nvarchar(max) NOT NULL,
			[Sources] nvarchar(max) NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorDataTables') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorDataTables](
			IndicatorId uniqueidentifier not null,
			CompanyId uniqueidentifier not null,
			IndicatorTemplateId uniqueidentifier not null,
			IndicatorNumber nvarchar(max) not null,
			Name nvarchar(max) null,  
			Year int null, 
			Value nvarchar(max) null
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.IndicatorTickboxes') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[IndicatorTickboxes](
			[IndicatorId] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[IndicatorTemplateId] uniqueidentifier NOT NULL,
			[TickboxCode] nvarchar(max) NOT NULL,
			[TickboxText] nvarchar(max) NOT NULL,
			[IsChecked] bit NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Sources') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[Sources](
			[SourceIdId] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[SourceText] nvarchar(max) NOT NULL,
			[IndicatorsNumber] nvarchar(max) NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.KeyESGIssueTemplates') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[KeyESGIssueTemplates](
		[Id] [uniqueidentifier] NOT NULL,
		[Name] [nvarchar](max) NOT NULL,
		[ExternalId] [int] NOT NULL,
		[Description] [nvarchar](max) NOT NULL
		) ON [PRIMARY]
	END

	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CompanyKeyESGIssues') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[CompanyKeyESGIssues](
		[Id] [uniqueidentifier] NOT NULL,
		[CompanyId] [uniqueidentifier] NOT NULL,
		[CompanyName] [nvarchar](max) NOT NULL,
		[SubIndustryCode] [int] NOT NULL,
		[Name] [nvarchar](max) NOT NULL,
		[ExternalId] [int] NOT NULL,
		[Description] [nvarchar](max) NOT NULL,
		[Comment] [nvarchar](max) NOT NULL,
		[SortKey] [int] NOT NULL
		) ON [PRIMARY]
	END
END

GO


